package com.towa.ninemanmorris;

import java.util.ArrayList;
import java.util.HashSet;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class HighscoreDB {
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	
	public HighscoreDB(Context context){
		dbHelper = new MySQLiteHelper(context);
	}
	
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}
	
	public ArrayList<HighscoreWrapper> getGroups(String username){
		ArrayList<HighscoreWrapper> hwList = new ArrayList<HighscoreWrapper>();
		Cursor cursor = database.query("highscore", null, "username = '" + username + "'", null, null, null, null);
		if(cursor.moveToFirst()){
			HashSet<String> names = new HashSet<String>();
			do{
				names.add(cursor.getString(1));
			}while(cursor.moveToNext());
			
			for(String s : names){
				HighscoreWrapper hw = getGroup(s, 10);
				if(hw.getMembers().size() > 0)
					hwList.add(hw);
			}
		}
		return hwList;
	}
	
	public HighscoreWrapper getGroup(String groupName, int nr){
		ArrayList<MemberWrapper> mwList = new ArrayList<MemberWrapper>();
		Cursor cursor = database.query("highscore", null, "groupname = '" + groupName + "'", null, null, null, null);
		if(cursor.moveToFirst()){
			do{
				MemberWrapper mw = new MemberWrapper();
				mw.setUsername(cursor.getString(2));
				mw.setWin(cursor.getInt(3));
				mw.setLoss(cursor.getInt(4));
				mwList.add(mw);
			}while(cursor.moveToNext());
		}
		if(mwList.size() > nr){
			mwList = (ArrayList<MemberWrapper>) mwList.subList(0, nr);
		}
		if(mwList.size() == 0)
			return null;
		return new HighscoreWrapper(groupName, mwList);
	}
	
	public void addOrUpdateHighscore(HighscoreWrapper hw){
		ArrayList<MemberWrapper> mwList = hw.getMembers();
		for(MemberWrapper mw : mwList){
			addOrUpdateMember(hw.getGroupName(), mw);
		}
	}
	
	public void addOrUpdateMember(String groupName, MemberWrapper mw){
		long id = getMemberId(groupName, mw.getUsername());
		if(id != -1){
			updateMember(id, groupName, mw);
		}else{
			insertMember(groupName, mw);
		}
	}
	
	private long getMemberId(String groupName, String memberName){
		Cursor cursor = database.query("highscore", null, "groupname = '" + groupName + "' and username = '" + memberName + "'", null, null, null, null);
		if(cursor.moveToFirst())
			return cursor.getLong(0);
		return -1;
	}
	
	private void updateMember(long id, String groupName, MemberWrapper mw){
		ContentValues values = new ContentValues();
			values.put("groupname", groupName);
			values.put("username", mw.getUsername());
			values.put("win", mw.getWin());
			values.put("loss", mw.getLoss());
			database.update("highscore", values, "_id = " + id, null);		
	}
	
	private void insertMember(String groupName, MemberWrapper mw){
		ContentValues values = new ContentValues();
		values.put("groupname", groupName);
		values.put("username", mw.getUsername());
		values.put("win", mw.getWin());
		values.put("loss", mw.getLoss());
		database.insert("highscore", null, values);
	}
	
	public class MySQLiteHelper extends SQLiteOpenHelper {

		public MySQLiteHelper(Context context) {
			super(context, "NineManMorrisHighscore.db", null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("create table highscore (_id integer primary key autoincrement, groupname text, username text not null, win integer, loss integer)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("drop table if exists hosts");
			onCreate(db);
		}
		
	}

}
